#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
import pymysql
import numpy as np


# 数据读取
host = "localhost"
user = "root"
passwd = "392892"
db = "course"
sql = "select * from 5_task;"
try:
    conn = pymysql.connect(host=host, user=user, passwd=passwd,db=db,charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
    cursor.execute(sql)
    res = cursor.fetchall()
    print("SQL执行成功！")
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    df = pd.DataFrame(res, columns=cols)
    print("完成！")
except Exception as e:
    print(e)


# In[2]:


df2 = df.copy()

# 选择用户资金流量数据
df2 = df2[["user_id", "report_date","tBalance", "yBalance"]]
user_info = df2["user_id"].value_counts()
user_ID = user_info.index
freq = user_info.values
df_user = pd.DataFrame(list(zip(user_ID, freq)), columns=["用户ID", "频次"])
df_user = df_user[df_user["频次"] > 400]  # 过滤掉出现频次低于阈值的客户。
ID = df_user["用户ID"][0]
df_s = df2[df2["user_id"] == ID].reset_index(drop=True)  # 选择想要预测的客户ID
df_s["report_date"] = pd.to_datetime(df_s["report_date"].map(str)) # 日期格式处理
df_s = df_s[["report_date","tBalance"]].drop_duplicates().sort_values(by="report_date", ascending=True, ignore_index=True).set_index("report_date")


# #### 识别模型的阶数

# ##### 根据平稳性检验和白噪声检验确定差分阶数I

# In[9]:


# 折线图展示数据大致趋势
import statsmodels.api as sm
from statsmodels.stats.diagnostic import acorr_ljungbox
from statsmodels.graphics.tsaplots import plot_pacf,plot_acf
p1 = np.round(sm.tsa.stattools.adfuller(df_s)[1],5)
p2 = sm.tsa.stattools.adfuller(df_s.diff()[1:])[1]
from matplotlib import pyplot as plt
plt.rcParams["axes.unicode_minus"] = False
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(8,6),dpi=100)
plt.plot([i for i in df_s.index],df_s, "--g")
plt.plot([i for i in df_s.index],df_s.diff(), "-b")
plt.legend([f"原始数据\n平稳性检验P值：{p1}",f"一阶差分\n平稳性检验P值：{p2}"])
plt.title("原始数据与一阶差分平稳性对比\n（平稳性检验的P值远小于0.1，则表明拒绝非平稳性的原假设，数据是平稳的）")
plt.xlabel("时间")
plt.ylabel("资金")
plt.tight_layout()
# plt.show()
# plt.savefig("图片保存路径")


# In[4]:


import statsmodels.api as sm
from statsmodels.stats.diagnostic import acorr_ljungbox
display("原始数据纯随机性检验：",acorr_ljungbox(df_s, lags = [6, 12], boxpierce = True))
display("一阶差分数据纯随机性检验：",acorr_ljungbox(df_s.diff()[1:], lags = [6, 12], boxpierce = True)) # 白噪声检验


# ##### 根据自相关图和偏自相关图分别确定阶数q和p

# In[11]:


fig = plt.figure(figsize=(20,8),dpi=100)
layout = (1, 2)
acf_ax = plt.subplot2grid(layout,(0,0))
pacf_ax = plt.subplot2grid(layout,(0,1))
acf = plot_acf(df_s, ax=acf_ax)
plt.title("自相关图")
plt.ylabel("ACF")
plt.xlabel("lag")

pacf = plot_pacf(df_s, method='ywm',ax=pacf_ax)
plt.title("偏自相关图")
plt.ylabel("PACF")
plt.xlabel("lag")
# plt.show()
plt.tight_layout()
# plt.show()
plt.savefig(r"保存路径")


# ##### 或者使用AIC系数和BIC系数选取

# In[16]:


# 确定阶数
import warnings;warnings.simplefilter("ignore")
trend_evaluate = sm.tsa.arma_order_select_ic(df_s.diff()[1:], ic=["aic", "bic"], trend="c",max_ar=4,max_ma=4)
print("train AIC",trend_evaluate.aic_min_order)
print("train BIC",trend_evaluate.bic_min_order)
p = trend_evaluate.bic_min_order[0]
q = trend_evaluate.bic_min_order[1]


# #### 建模：ARIMA(p,I,q)

# In[26]:


# 拆分训练集和测试集
train = df_s.loc["2013":"2014"]
test = df_s.loc["2014-08"]


# In[18]:


# 建模
model = sm.tsa.arima.ARIMA(df_s, order=(p, 1, q))
arima_res = model.fit()
display(arima_res.summary())


# #### 模型评估

# In[34]:


# 模型评估
# name = df["原料名称"][0]
from sklearn.metrics import r2_score, mean_absolute_error

predict = arima_res.predict("2014-08-01","2014-08-31")
rmse = np.sqrt(mean_absolute_error(test, predict))
plt.figure(figsize=(20,8),dpi=120)
plt.subplot(221)
plt.plot(predict.index, predict, label = "预测值")
plt.plot(test.index, test, label="真实值")
plt.legend(loc="best")
plt.title(f"均方误差：{rmse}")
plt.xlabel("日期")
plt.ylabel(f"用户{ID}资金流量预测")

plt.subplot(222)
residual = list(list(test["tBalance"] - predict))
plt.plot(residual, label='残差')
plt.title("残差检验")
plt.legend(loc="best")

import seaborn as sns
from scipy import stats
import warnings;warnings.simplefilter("ignore")
plt.subplot(223)
sns.distplot(residual, fit=stats.norm)
plt.xlabel("残差正态性检验")
plt.subplot(224)
res = stats.probplot(residual,plot=plt)
plt.xlabel("QQ-Plot")
# plt.show()
plt.tight_layout()
plt.savefig(r"保存路径")


# #### 预测未来5天的数数据

# In[38]:


forest = arima_res.forecast(5)
# date = pd.date_range(start=train.index[-1], periods=len(forest))  # ferq="B"代表工作日
df_for = pd.DataFrame(forest)
# df_for.index = date
df_for.plot(style="bo--",figsize=(10,5))
plt.xlabel("日期", fontdict={"fontsize":14})
plt.ylabel(f"未来5天用户{ID}资金流量预测",fontdict={"fontsize":14})
for a,b in zip(df_for.index, np.round(df_for["predicted_mean"],2)):
    plt.text(a,b+0.004,b,ha="left", va="bottom", color="red",fontsize=10)
# plt.show()
plt.tight_layout()
plt.savefig(r"保存路径")

